--    Author    : ChenErHao
--    Name      : DM.T_MTH_BAD_LOAN_BALANCE_QUALITY.HQL
--    Functions : 风险-不良贷款质量迁徙分析
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_BAD_LOAN_BALANCE_QUALITY PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
SELECT 
  A.MONTH_ID FISCAL_MTH_DIMNSN_ID_L,
  nvl(B.BELONG_AREA_CODE,9999) AREA_NO_ID, 
  A.LOAN_QUALITY LOAN_QUALITY_R,
  B.LOAN_QUALITY LOAN_QUALITY_L,
  nvl(SUM(B.LOAN_BALANCE),0) LOAN_BALANCE
   FROM 
 (SELECT DATA_DATE MONTH_ID,LOAN_QUALITY,LOAN_IOUS_CODE FROM EDW.LOAB WHERE DATA_DATE between '#V_LAST_17M_END_DATE#' and '#V_LAST_1M_END_DATE#'  ) A,
 (SELECT DATA_DATE MONTH_ID,LOAN_QUALITY,LOAN_IOUS_CODE,NVL(BELONG_AREA_CODE,'9999')  BELONG_AREA_CODE,LOAN_BALANCE FROM EDW.LOAB A 
        WHERE DATA_DATE='#V_DATA_DATE#') B
  WHERE A.LOAN_IOUS_CODE=B.LOAN_IOUS_CODE 
  GROUP BY 
  B.MONTH_ID,
  A.MONTH_ID,
  B.BELONG_AREA_CODE,
  A.LOAN_QUALITY,
  B.LOAN_QUALITY ;